package com.probiz.estore.content.dao.impl;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import org.apache.commons.lang.ArrayUtils;
import org.apache.commons.lang.StringUtils;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;

import com.probiz.estore.catalog.dao.ContentCategoryDao;
import com.probiz.estore.common.model.catalog.ContentCategory;
import com.probiz.estore.common.model.content.Content;
import com.probiz.estore.common.model.other.Tag;
import com.probiz.estore.common.util.SearchUtil;
import com.probiz.estore.content.dao.ContentDao;
import com.probiz.estore.content.model.ContentSearchCriteria;
import com.probiz.estore.core.dao.impl.HibernateGenericDaoImpl;
import com.probiz.estore.core.search.SearchCriteria;

/**
 * Dao implementation for Content.
 */
public class ContentDaoImpl extends HibernateGenericDaoImpl<Content> implements ContentDao {
    private ContentCategoryDao contentCategoryDao ;
    
    
	public void setContentCategoryDao(ContentCategoryDao contentCategoryDao) {
		this.contentCategoryDao = contentCategoryDao;
	}

	/*
      * (non-Javadoc)
      * @see com.probiz.estore.content.dao.ContentDao#searchByCriteria(com.probiz.estore.content.model.ContentSearchCriteria, com.probiz.estore.core.search.SearchCriteria)
      */
    public List<Content> searchByCriteria(ContentSearchCriteria contentSearchCriteria, SearchCriteria searchCriteria) {
    	List<Object> paramList = new ArrayList<Object>();
        searchCriteria.setHql(getSearchHql(contentSearchCriteria,paramList));
        searchCriteria.removeParamValues();
        for (Object object : paramList) {
            searchCriteria.addParamValue(object);
        }
        return searchByCriteria(searchCriteria);
    }
    
    public List<Content> getContentList(final ContentSearchCriteria contentSearchCriteria,final int firstResult, final int maxResults){
    	List list;
		list = getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				List<Object> paramList = new ArrayList<Object>();
				String hql = getSearchHql(contentSearchCriteria,paramList);
				
				Query query = session.createQuery(hql);
				for(int i=0; i< paramList.size(); i++){
					query.setParameter(i, paramList.get(i));
				}
				query.setFirstResult(firstResult);
				if(maxResults!=-1)query.setMaxResults(maxResults);
				List<Content> rows = (List<Content>) query.list();
				
				return rows;
			}

		});
		return list;
    }
    
    /**
     * 获得搜索hql
     * @param contentSearchCriteria 搜索条件
     * @param paramList 参数列表
     * @return 搜索hql
     */
    private String getSearchHql(ContentSearchCriteria contentSearchCriteria,List<Object> paramList){
    	 //-begin:获得所有符合的标签id
    	Set<Integer> tagIdSet = new HashSet<Integer>();
        if (StringUtils.isNotBlank(contentSearchCriteria.getTags())) {
        	String[] tagNameArray = contentSearchCriteria.getTags().trim().replaceAll("\\s+",",").split(",");
        	for(int i=0; i< tagNameArray.length; i++){
        		tagIdSet.addAll(this.findByHql("select t.tagId from Tag t where t.tagName like ? and t.module=?", "%"+tagNameArray[i]+"%",Tag.MODULE_CONTENT));
        	}
        }
        //-end:获得所有符合的标签id
        
    	StringBuffer hqlsb = new StringBuffer(100);
       
        //start:构建HQL语句
        hqlsb.append("from Content c where 1=1 ");
        //-begin:关键词
        if (StringUtils.isNotBlank(contentSearchCriteria.getKeywords())) {
            String keywords = SearchUtil.getSqlQueryValue(contentSearchCriteria.getKeywords());
            hqlsb.append(" and (");
            hqlsb.append("    c.contentTitle like ? ");
            hqlsb.append(" or c.metaKeyword like ? ");
            hqlsb.append(" or c.sourceName like ? ");
            hqlsb.append(" or c.category.categoryName like ? ");
            hqlsb.append(" or c.category.keywords like ? ");
            hqlsb.append(" )");
            for (int i = 0; i < 5; i++) {
                paramList.add("%" + keywords + "%");
            }
        }
        //-end:关键词
        //-begin:不包含关键词
        if (StringUtils.isNotBlank(contentSearchCriteria.getExcludedKeywords())) {
            String excludedKeywords = SearchUtil.getSqlQueryValue(contentSearchCriteria.getExcludedKeywords());
            hqlsb.append(" and ");
            hqlsb.append("(c.contentTitle not like ? ");
            hqlsb.append(" and c.metaKeyword not like ? )");
            for (int i = 0; i < 2; i++) {
                paramList.add("%" + excludedKeywords + "%");
            }
        }
        //-end:不包含关键词

        //-begin:来源名称
        if (StringUtils.isNotBlank(contentSearchCriteria.getSourceName())) {
            hqlsb.append(" and c.sourceName like ?");
            paramList.add("%" + contentSearchCriteria.getSourceName() + "%");
        }
        //-end:来源名称
        //-begin:文章目录
        if (!ArrayUtils.isEmpty(contentSearchCriteria.getCategoryPaths())) {
            hqlsb.append(" and (");
            for (int i = 0; i < contentSearchCriteria.getCategoryPaths().length; i++) {
                hqlsb.append("c.category.categoryPath like ?");
                if (i < contentSearchCriteria.getCategoryPaths().length - 1) hqlsb.append(" or ");
                paramList.add(contentSearchCriteria.getCategoryPaths()[i] + "%");
            }
            hqlsb.append(" )");
        }
        if (StringUtils.isNotBlank(contentSearchCriteria.getCategoryPath())) {
            hqlsb.append(" and (");
            hqlsb.append("c.category.categoryPath like ?");
            hqlsb.append(" )");
            paramList.add(contentSearchCriteria.getCategoryPath() + "%");

        }
        
        if (StringUtils.isNotBlank(contentSearchCriteria.getCategoryId())) {
        	hqlsb.append(" and (");
        	hqlsb.append("c.category.categoryId = ?");
        	hqlsb.append(" )");
        	paramList.add(new Integer(contentSearchCriteria.getCategoryId()));
        	
        }
        //-end:文章目录
      //-begin:标签名称
        if (StringUtils.isNotBlank(contentSearchCriteria.getTags())) {
        	hqlsb.append(" and (");
        	hqlsb.append(" 1=0 ");
        	for(Integer tagId: tagIdSet){
        		hqlsb.append(" or  ','||c.tags||',' like ?");
        		paramList.add("%,"+tagId+",%");
        	}
        	hqlsb.append(" )");
        	
        }
        //-end:标签名称
        //-start:状态
        if (null != contentSearchCriteria.getStatus()) {
            hqlsb.append(" and c.status = ?");
            paramList.add(contentSearchCriteria.getStatus());
        }
        //-end:状态
        //start:order by
        if (null != contentSearchCriteria.getOrderby()) {
            hqlsb.append(" order by ").append(getSortByString(contentSearchCriteria.getOrderby())).append(",c.contentId desc");
        }else{
        	hqlsb.append(" order by c.contentId desc");
        }
        //end:order by
        //end:构建HQL语句
        return hqlsb.toString();
    	
    }


    /**
     * 根据orderby获得 hql语句
     *
     * @param orderby 排序id
     * @return hql语句
     */
    private String getSortByString(short orderby) {
        String orderByString;
        switch (orderby) {
            case 1:
                orderByString = "c.sortOrder asc";
                break;
            case 3:
            	orderByString = "c.contentTitle asc";
            	break;
            case 4:
            	orderByString = "c.contentTitle desc";
            	break;
            case 5:
            	orderByString = "convert_gbk(c.category.categoryName) asc";
            	break;
            case 6:
            	orderByString = "convert_gbk(c.category.categoryName) desc";
            	break;
            case 7:
            	orderByString = "updateTime asc";
            	break;
            case 8:
            	orderByString = "updateTime desc";
            	break;
            case 9:
            	orderByString = "status*-1 asc";
            	break;
            case 10:
            	orderByString = "status*-1 desc";
            	break;
            default:
                orderByString = "c.contentId desc";
                break;
        }
        return orderByString;
    }

    /*
      * (non-Javadoc)
      * @see com.probiz.estore.content.dao.ContentDao#countContentByCategoryId(java.lang.Integer)
      */
    public Integer countContentByCategoryId(Integer categoryId) {
        String hql = "select count(c.contentId) from Content c where c.category.categoryId=?";
        Long itemCount = (Long) this.findUnique(hql, categoryId);
        return new Integer(itemCount.toString());
    }

    /*
      * (non-Javadoc)
      * @see com.probiz.estore.content.dao.ContentDao#countContentIncludeAllByCategory(com.probiz.estore.catalog.model.ContentCategory)
      */
    public Integer countContentIncludeAllByCategory(ContentCategory contentCategory) {
        String hql = "select count(c.contentId) from Content c where c.category.categoryPath like ?";
        Long itemCount = (Long) this.findUnique(hql, contentCategory.getCategoryPath() + "%");
        return new Integer(itemCount.toString());
    }

    /*
      * (non-Javadoc)
      * @see com.probiz.estore.content.dao.ContentDao#countActiveContentIncludeAllByCategory(com.probiz.estore.catalog.model.ContentCategory)
      */
    public Integer countActiveContentIncludeAllByCategory(ContentCategory contentCategory) {
        String hql = "select count(c.contentId) from Content c where c.status =1 and c.category.categoryPath like ?";
        Long itemCount = (Long) this.findUnique(hql, contentCategory.getCategoryPath() + "%");
        return new Integer(itemCount.toString());
    }

    /*
      * (non-Javadoc)
      * @see com.probiz.estore.content.dao.ContentDao#getAllExpiredContents()
      */
    public List<Content> getAllExpiredContents() {
        List list;
        list = getHibernateTemplate().executeFind(new HibernateCallback() {
            public Object doInHibernate(Session session)
                    throws HibernateException, SQLException {
                Date now = new Date();
                StringBuffer sb = new StringBuffer();
                sb.append("from Content c where c.status =1 and ");
                sb.append("(c.expiredTime is not null and c.expiredTime < :now )");
                Query query = session.createQuery(sb.toString());
                query.setTimestamp("now", now);
                return query.list();
            }

        });
        return list;
    }

    /*
      * (non-Javadoc)
      * @see com.probiz.estore.content.dao.ContentDao#getAllToPulishedContents()
      */
    public List<Content> getAllToPulishedContents() {
        List list;
        list = getHibernateTemplate().executeFind(new HibernateCallback() {
            public Object doInHibernate(Session session)
                    throws HibernateException, SQLException {
                Date now = new Date();
                StringBuffer sb = new StringBuffer();
                sb.append("from Content c where c.status =0 and c.isShow = 1 and ");
                sb.append(" c.publishTime < :now and ");
                sb.append("(c.expiredTime is  null or c.expiredTime > :now )");
                Query query = session.createQuery(sb.toString());
                query.setTimestamp("now", now);
                return query.list();
            }

        });
        return list;
    }

    /*
      * (non-Javadoc)
      * @see com.probiz.estore.content.dao.ContentDao#getRecommendedContents(com.probiz.estore.catalog.model.ContentCategory, int)
      */
    public List<Content> getRecommendedContents(final ContentCategory category, final int size) {
        List list;
        list = getHibernateTemplate().executeFind(new HibernateCallback() {
            public Object doInHibernate(Session session)
                    throws HibernateException, SQLException {
                StringBuffer sb = new StringBuffer();
                sb.append("from Content c where c.status =1 and c.category.categoryPath like :path order by c.sortOrder asc");
                Query query = session.createQuery(sb.toString());
                query.setString("path", category.getCategoryPath());
                query.setMaxResults(size);
                return query.list();
            }
        });
        return list;
    }

    /*
      * (non-Javadoc)
      * @see com.probiz.estore.content.dao.ContentDao#getNewestContents(com.probiz.estore.catalog.model.ContentCategory, int)
      */
    public List<Content> getNewestContents(final ContentCategory category, final int size) {
        List list;
        list = getHibernateTemplate().executeFind(new HibernateCallback() {
            public Object doInHibernate(Session session)
                    throws HibernateException, SQLException {
                StringBuffer sb = new StringBuffer();
                sb.append("from Content c where c.status =1 and c.category.categoryPath like :path order by c.publishTime desc");
                Query query = session.createQuery(sb.toString());
                query.setString("path", category.getCategoryPath());
                query.setMaxResults(size);
                return query.list();
            }
        });
        return list;
    }

    /*
      * (non-Javadoc)
      * @see com.probiz.estore.content.dao.ContentDao#getRelatedContents(java.lang.Integer, int)
      */
    public List<Content> getRelatedContents(final Integer contentId, final int size) {
        final Content content = getById(contentId);
        String[] tempKeywordList = null;
        if (StringUtils.isNotBlank(content.getMetaKeyword().trim()))
            tempKeywordList = content.getMetaKeyword().trim().replace('，', ',').split(",");
        final String[] keywordList = tempKeywordList;
        List list;
        list = getHibernateTemplate().executeFind(new HibernateCallback() {
            public Object doInHibernate(Session session)
                    throws HibernateException, SQLException {
                StringBuffer sb = new StringBuffer();
                sb.append("from Content c where c.status =1 and c.category.categoryPath like :path ");
                if (!ArrayUtils.isEmpty(keywordList)) {
                    sb.append(" and (  ");
                    for (String keyword : keywordList) {
                        if (StringUtils.isNotEmpty(keyword)) {
                            sb.append(" c.contentTitle like ").append("'%").append(keyword).append("%' or ");
                            sb.append(" ifnull(c.metaKeyword,false) like ").append("'%").append(keyword).append("%' or ");
                        }
                    }
                    sb.append(" 1=0 ) ");
                }
                sb.append(" and c.contentId != ").append(content.getContentId());
                sb.append(" order by c.sortOrder asc");
                Query query = session.createQuery(sb.toString());
                query.setString("path", content.getCategory().getCategoryPath());
                query.setMaxResults(size);
                return query.list();
            }
        });
        return list;
	}
    /*
     * (non-Javadoc)
     * @see com.probiz.estore.content.dao.ContentDao#updateStatField(java.lang.Integer, java.lang.String, java.lang.Integer)
     */
    public void updateStatField(final Integer contentStatId,final String fieldName,final Integer count){
    	getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session) throws HibernateException, SQLException {
				StringBuffer sb = new StringBuffer();
				sb.append("update content_stat set ").append(fieldName).append(" = ").append(fieldName);
				if(count>=0){
					sb.append(" + ").append(count);
				}else{
					sb.append(" - ").append(count);
				}
				sb.append(" where contentStatId=").append(contentStatId);
				int i=session.createSQLQuery(sb.toString()).executeUpdate();
				return i;
			}

		});
    }
}
